package com.project.dao.imp;

import com.project.bean.*;
import com.project.dao.IRoomDao;
import org.springframework.stereotype.Repository;

import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

@Repository
public class IRoomDaoImp extends BaseDao implements IRoomDao {
    @Override
    public CutPageBean<RoomBean> findByItem(Integer pageNO, String address, String state, String type, String live) {
        CutPageBean<RoomBean> cut = new CutPageBean<>();
        this.setConnection();
        String sql = "SELECT r.*,COUNT(s.`fk_roomId`)c FROM r_room r LEFT JOIN r_student s ON s.`fk_roomId`=r.`pk_roomId` where 1=1 ";
        String dynaSql = "";
        String dyna1 = "";
        if (address != null && address.length() != 0) {
            dynaSql += "AND r.`r_roomAdress` LIKE '%" + address + "%' ";
        }
        if (state != null && state.length() != 0) {
            dynaSql += "AND `r_state` = '" + state + "'";
        }
        if (type != null && type.length() != 0) {
            dynaSql += "AND `r_roomType`= '" + type + "' ";
        }
        if (live != null && live.length() != 0) {
            dyna1 += "having c <r.`r_maxNum`";
        }
        try {
            ps = con.prepareStatement(sql + dynaSql + " GROUP BY r.`pk_roomId` " + dyna1 + " LIMIT ?,?");
            ps.setObject(1, (pageNO - 1) * CutPageBean.PAGESIZE);
            ps.setObject(2, CutPageBean.PAGESIZE);
            re = ps.executeQuery();
            while (re.next()) {
                cut.getList().add(this.getRoom());
            }
            ps = con.prepareStatement("select count(*)num from (" + sql + dynaSql + " GROUP BY r.`pk_roomId` " + dyna1 + ") c");
            re = ps.executeQuery();
            while (re.next()) {
                cut.setCount(re.getInt("num"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            this.closeConnection();
        }
        return cut;
    }


    @Override
    public RoomBean findById(Integer id) {
        RoomBean roomBean = null;
        this.setConnection();
        try {
//            ps = con.prepareCall("select * from r_room where pk_roomId = ?");
            ps = con.prepareCall("select *,COUNT(fk_roomId) n from r_room,r_student where pk_roomId = ? and pk_roomId = fk_roomId");
            ps.setObject(1, id);

            re = ps.executeQuery();
            if (re.next()) {
                roomBean = new RoomBean();
                roomBean.setId(re.getInt("pk_roomId"));
                roomBean.setAddress(re.getString("r_roomAdress"));
                roomBean.setMaxStu(re.getInt("r_maxNum"));
                roomBean.setState(re.getString("r_state"));
                roomBean.setType(re.getString("r_roomType"));
                roomBean.setStuNum(re.getInt("n"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConnection();
        }
        return roomBean;
    }

    public RoomBean getRoom() {
        RoomBean roomBean = new RoomBean();
        try {
            roomBean.setId(re.getInt("pk_roomId"));
            roomBean.setAddress(re.getString("r_roomAdress"));
            roomBean.setType(re.getString("r_roomType"));
            roomBean.setMaxStu(re.getInt("r_maxNum"));
            roomBean.setState(re.getString("r_state"));
            roomBean.setStuNum(re.getInt("c"));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return roomBean;
    }

    @Override
    public boolean del(Integer id) {
        this.setConnection();
        try {
            ps = con.prepareCall("delete from r_room where pk_roomId = ?");
            ps.setObject(1, id);

            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConnection();
        }
        return false;
    }

    @Override
    public List<RoomBean> findBySex(String sex) {
        List<RoomBean> list = new ArrayList<>();
        this.setConnection();
        try {
            ps = con.prepareCall("select * ,count(fk_roomId)c from r_room  LEFT JOIN  r_student sr on pk_roomId = fk_roomId GROUP BY(pk_roomId) HAVING c<r_maxNum and r_roomType = ?");
            ps.setObject(1, sex);

            re = ps.executeQuery();
            while (re.next()) {
                RoomBean roomBean = new RoomBean();
                roomBean.setId(re.getInt("pk_roomId"));
                roomBean.setAddress(re.getString("r_roomAdress"));
                roomBean.setMaxStu(re.getInt("r_maxNum"));
                roomBean.setStuNum(re.getInt("c"));
                roomBean.setState(re.getString("r_state"));
                roomBean.setType(re.getString("r_roomType"));
                list.add(roomBean);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConnection();
        }
        return list;
    }

    @Override
    public void add(RoomBean roomBean) {
        System.out.println(roomBean);
        this.setConnection();
        try {
            ps = con.prepareCall("INSERT INTO r_room (r_roomAdress,r_maxNum,r_state,r_roomType)" +
                    "VALUES(?,?,?,?)");
            ps.setObject(1, roomBean.getAddress());
            ps.setObject(2, roomBean.getMaxStu());
            ps.setObject(3, roomBean.getState());
            ps.setObject(4, roomBean.getType());

            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConnection();
        }
    }

    @Override
    public void updateRoomStatus(Integer id, String roomStatus) {
        this.setConnection();
        try {
            ps = con.prepareCall("update r_room set r_state = ? where pk_roomId = ?");
            ps.setObject(1, roomStatus);
            ps.setObject(2, id);

            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConnection();
        }
    }

    @Override
    public List<RoomBean> findByOkRoom() {
        List<RoomBean> list = new ArrayList<>();
        this.setConnection();
        try {
            ps = con.prepareCall("select *,COUNT(fk_roomId) n from r_room LEFT JOIN r_student on  pk_roomId = fk_roomId GROUP BY pk_roomId");
            re = ps.executeQuery();
            while (re.next()) {
                RoomBean roomBean = new RoomBean();
                roomBean.setId(re.getInt("pk_roomId"));
                roomBean.setAddress(re.getString("r_roomAdress"));
                roomBean.setMaxStu(re.getInt("r_maxNum"));
                roomBean.setState(re.getString("r_state"));
                roomBean.setType(re.getString("r_roomType"));
                roomBean.setStuNum(re.getInt("n"));
                list.add(roomBean);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConnection();
        }
        return list;
    }

    @Override
    public RoomBean findByName(String name) {
        RoomBean roomBean = null;
        this.setConnection();
        try {
            ps = con.prepareCall("select * from r_room where r_roomAdress = ? ");
            ps.setObject(1, name);

            re = ps.executeQuery();
            if (re.next()) {
                roomBean = new RoomBean();
                roomBean.setId(re.getInt("pk_roomId"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConnection();
        }
        return roomBean;
    }
}
